﻿Option Strict On
Option Explicit On
Option Infer On

Imports System
Imports System.Data.SqlClient


Public Class LoadSqlScripts
   Public Shared Sub RunScript(ByVal fileName As String, ByVal connection As SqlConnection)
      Dim cmd As New Data.SqlClient.SqlCommand
      Dim reader As IO.StreamReader = Nothing
      Dim sProcName As String
      Dim iProcStatementPos As Int32
      If IO.File.Exists(fileName) Then
         Try
            reader = New IO.StreamReader(fileName)
            Dim statements() As String = Text.RegularExpressions.Regex.Split( _
                     reader.ReadToEnd, "\sgo\s", _
                     Text.RegularExpressions.RegexOptions.Compiled Or _
                     Text.RegularExpressions.RegexOptions.CultureInvariant Or _
                     Text.RegularExpressions.RegexOptions.IgnoreCase)
            iProcStatementPos = GetProcStatementPosition(statements)
            sProcName = RetrieveStoredProcName(statements, iProcStatementPos)
            Try
               connection.Open()
               'trans = mconnection.BeginTransaction
               cmd.Connection = connection
               'cmd.Transaction = trans
               cmd.CommandType = Data.CommandType.Text

               For i As Int32 = 0 To statements.Length - 1
                  If Not String.IsNullOrEmpty(statements(i)) Then
                     cmd.CommandText = statements(i)
                     cmd.ExecuteNonQuery()
                  End If
               Next

            Catch ex As Exception
               Diagnostics.Debug.WriteLine(ex)
               Throw
            Finally
               cmd.Connection.Close()
            End Try
         Catch ex As System.Exception
            Throw
         Finally
            If reader IsNot Nothing Then
               reader.Close()
            End If
         End Try
      End If
   End Sub

   Private Shared Function GetProcStatementPosition( _
                  ByVal statements() As String) _
                  As Int32
      ' TODO: Replace this with Regex that avoids finding CreateProcedure in a comment
      '       and in other ways might need to be more sophisticated
      For i As Int32 = 0 To statements.GetUpperBound(0)
         Dim s() As String = _
               Text.RegularExpressions.Regex.Split(statements(i), "\W+create\W+procedure\W+", _
               Text.RegularExpressions.RegexOptions.Compiled Or _
               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
               Text.RegularExpressions.RegexOptions.IgnoreCase)
         If s.GetLength(0) > 1 Then
            Return i
         End If
      Next
   End Function

   Private Shared Function RetrieveStoredProcName( _
                     ByVal statements() As String, _
                     ByVal iProcPos As Int32) _
                     As String
      Dim sprocName As String
      ' TODO: Replace this with Regex that avoids finding CreateProcedure in a comment
      '       and in other ways might need to be more sophisticated

      Dim s() As String = _
            Text.RegularExpressions.Regex.Split(statements(iProcPos), "\W+create\W+procedure\W+", _
            Text.RegularExpressions.RegexOptions.Compiled Or _
            Text.RegularExpressions.RegexOptions.CultureInvariant Or _
            Text.RegularExpressions.RegexOptions.IgnoreCase)
      If s.Length = 1 Then
         s = Text.RegularExpressions.Regex.Split(statements(iProcPos), "\W+create\W+view\W+", _
               Text.RegularExpressions.RegexOptions.Compiled Or _
               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
               Text.RegularExpressions.RegexOptions.IgnoreCase)
      End If
      If s.GetLength(0) > 1 Then
         sprocName = s(1).Trim
         ' Separate into individual words
         Dim sWords() As String = _
               Text.RegularExpressions.Regex.Split(sprocName, "\W+\w+\W+", _
               Text.RegularExpressions.RegexOptions.Compiled Or _
               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
               Text.RegularExpressions.RegexOptions.IgnoreCase)
         If sWords.GetLength(0) > 0 Then
            Return sWords(0)
         End If
      End If
      Return Nothing
   End Function

   'Private Shared Function RetrieveStoredProcContents( _
   '                  ByVal procName As String, _
   '                  ByVal runSqlScriptDirective As CodeGenerationSupport.IRunSQLScriptDirective) _
   '                  As String
   '   Dim databaseName As String = runSqlScriptDirective.Database
   '   Dim sqlText As String = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES " & _
   '           " WHERE ROUTINE_CATALOG='" & databaseName & _
   '           "' AND ROUTINE_TYPE='PROCEDURE'" & _
   '           " AND ROUTINE_NAME='" & procName & "'"
   '   Dim da As New Data.SqlClient.SqlDataAdapter(sqlText, mconnection)
   '   Dim dt As New Data.DataTable
   '   da.Fill(dt)
   '   If dt.Rows.Count > 0 Then
   '      Return dt.Rows(0)("ROUTINE_DEFINITION").ToString
   '   End If
   '   Return Nothing
   'End Function


End Class


'Public Class RunSqlScripts

'#Region "Class level declarations"
'   Private mconnection As Data.SqlClient.SqlConnection
'#End Region

'#Region "Public properties and methods"
'   Public Sub Run( _
'                  ByVal node As Xml.XmlNode, _
'                  ByVal runUtility As RunUtility, _
'                  ByVal statusDirective As IStatusDirective)
'      Dim runSqlScriptsDirective As CodeGenerationSupport.IRunSQLScriptDirective
'      Dim multiPassDirective As CodeGenerationSupport.IMultiPassDirective

'      runSqlScriptsDirective = CType(node, CodeGenerationSupport.IRunSQLScriptDirective)
'      multiPassDirective = CType(node, CodeGenerationSupport.IMultiPassDirective)
'      Try
'         mconnection = New Data.SqlClient.SqlConnection( _
'               "integrated security=SSPI" & _
'               ";data source=" & runSqlScriptsDirective.Server & _
'               ";persist security info=False" & _
'               ";initial catalog=" & runSqlScriptsDirective.Database)
'         'mConnection = New Data.SqlClient.SqlConnection( _
'         '      "workstation id=" & runSqlScriptsDirective.Server & _
'         '      ";packet size=4096;integrated security=SSPI" & _
'         '      ";data source=" & runSqlScriptsDirective.Server & _
'         '      ";persist security info=False" & _
'         '      ";initial catalog=" & runSqlScriptsDirective.Database)
'         Me.MultiPass(multiPassDirective, _
'                  Nothing, Nothing, _
'                  AddressOf RunSqlScriptsCallBack, _
'                  statusDirective, _
'                  runUtility)
'      Catch ex As System.Exception
'         ' Exception should already be recorded
'      End Try
'   End Sub
'#End Region

'   '#Region "Protected properties and methods"
'   '#End Region

'#Region "Private properties and methods"
'   Private Function RunSqlScriptsCallBack( _
'            ByVal outputFileName As String, _
'            ByVal multiPassDirective As CodeGenerationSupport.IMultiPassDirective, _
'            ByVal nodeSelect As Xml.XmlNode, _
'            ByVal runUtility As CodeGenerationSupport.RunUtility, _
'            ByVal statusOutput As CodeGenerationSupport.IStatusOutput) _
'            As IO.Stream
'      RunScript(multiPassDirective, outputFileName, runUtility, statusOutput)
'      Return Nothing
'   End Function


'   Public Sub RunScript( _
'            ByVal multiPassDirective As CodeGenerationSupport.IMultiPassDirective, _
'            ByVal filename As String, _
'            ByVal runUtility As CodeGenerationSupport.RunUtility, _
'            ByVal statusOutput As CodeGenerationSupport.IStatusOutput)
'      Const source As String = "RunSQLScripts.RunScript"
'      Dim trans As Data.SqlClient.SqlTransaction
'      Dim cmd As New Data.SqlClient.SqlCommand
'      Dim reader As IO.StreamReader = Nothing
'      Dim fileChanged As Utility.FileChanged
'      Dim sProcOldCode As String
'      Dim sProcNewCode As String
'      Dim sProcName As String
'      Dim iProcStatementPos As Int32
'      Dim runSqlScriptDirective As CodeGenerationSupport.IRunSQLScriptDirective
'      Dim outputRule As CodeGenerationSupport.IOutputRuleSupportElement
'      runSqlScriptDirective = CType(multiPassDirective, CodeGenerationSupport.IRunSQLScriptDirective)
'      outputRule = runSqlScriptDirective.OutputRule
'      If IO.File.Exists(filename) Then
'         Try
'            reader = New IO.StreamReader(filename)
'            Dim statements() As String = Text.RegularExpressions.Regex.Split( _
'                     reader.ReadToEnd, "\sgo\s", _
'                     Text.RegularExpressions.RegexOptions.Compiled Or _
'                     Text.RegularExpressions.RegexOptions.CultureInvariant Or _
'                     Text.RegularExpressions.RegexOptions.IgnoreCase)
'            iProcStatementPos = GetProcStatementPosition(statements)
'            sProcName = RetrieveStoredProcName(statements, iProcStatementPos)
'            sProcOldCode = RetrieveStoredProcContents(sProcName, runSqlScriptDirective)
'            If sProcOldCode Is Nothing Then
'               fileChanged = Utility.FileChanged.FileDoesntExist
'            Else
'               fileChanged = runUtility.IsTextChanged(sProcOldCode, outputRule.OutputFileType)
'            End If
'            If runUtility.ShouldRun(IO.Path.GetFileName(filename), _
'                     outputRule.OutputGenType, _
'                     outputRule.HashWhitespace, _
'                     outputRule.OutputFileType, statusOutput) Then
'               Try
'                  mconnection.Open()
'                  'trans = mconnection.BeginTransaction
'                  cmd.Connection = mconnection
'                  'cmd.Transaction = trans
'                  cmd.CommandType = Data.CommandType.Text

'                  For i As Int32 = 0 To statements.Length - 1
'                     If i = iProcStatementPos Then
'                        sProcNewCode = runUtility.ApplyHash(statements(i), _
'                                 outputRule.OutputGenType, outputRule.OutputFileType)
'                        cmd.CommandText = sProcNewCode
'                     Else
'                        cmd.CommandText = statements(i)
'                     End If
'                     cmd.ExecuteNonQuery()
'                  Next
'                  'trans.Commit()

'               Catch ex As Exception
'                  Diagnostics.Debug.WriteLine(ex)
'                  Throw
'               Finally
'                  cmd.Connection.Close()
'               End Try
'            End If
'         Catch ex As System.Exception
'            Throw
'         Finally
'            If reader IsNot Nothing Then
'               reader.Close()
'            End If
'         End Try
'      Else
'         statusOutput.AddStatusFailure(source, _
'                  "Could not find script file", Nothing)
'      End If

'   End Sub

'   Private Function GetProcStatementPosition( _
'                     ByVal statements() As String) _
'                     As Int32
'      ' TODO: Replace this with Regex that avoids finding CreateProcedure in a comment
'      '       and in other ways might need to be more sophisticated
'      For i As Int32 = 0 To statements.GetUpperBound(0)
'         Dim s() As String = _
'               Text.RegularExpressions.Regex.Split(statements(i), "\W+create\W+procedure\W+", _
'               Text.RegularExpressions.RegexOptions.Compiled Or _
'               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
'               Text.RegularExpressions.RegexOptions.IgnoreCase)
'         If s.GetLength(0) > 1 Then
'            Return i
'         End If
'      Next
'   End Function

'   Private Function RetrieveStoredProcName( _
'                     ByVal statements() As String, _
'                     ByVal iProcPos As Int32) _
'                     As String
'      Dim sprocName As String
'      ' TODO: Replace this with Regex that avoids finding CreateProcedure in a comment
'      '       and in other ways might need to be more sophisticated

'      Dim s() As String = _
'            Text.RegularExpressions.Regex.Split(statements(iProcPos), "\W+create\W+procedure\W+", _
'            Text.RegularExpressions.RegexOptions.Compiled Or _
'            Text.RegularExpressions.RegexOptions.CultureInvariant Or _
'            Text.RegularExpressions.RegexOptions.IgnoreCase)
'      If s.Length = 1 Then
'         s = Text.RegularExpressions.Regex.Split(statements(iProcPos), "\W+create\W+view\W+", _
'               Text.RegularExpressions.RegexOptions.Compiled Or _
'               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
'               Text.RegularExpressions.RegexOptions.IgnoreCase)
'      End If
'      If s.GetLength(0) > 1 Then
'         sprocName = s(1).Trim
'         ' Separate into individual words
'         Dim sWords() As String = _
'               Text.RegularExpressions.Regex.Split(sprocName, "\W+\w+\W+", _
'               Text.RegularExpressions.RegexOptions.Compiled Or _
'               Text.RegularExpressions.RegexOptions.CultureInvariant Or _
'               Text.RegularExpressions.RegexOptions.IgnoreCase)
'         If sWords.GetLength(0) > 0 Then
'            Return sWords(0)
'         End If
'      End If
'      Return Nothing
'   End Function

'   Private Function RetrieveStoredProcContents( _
'                     ByVal procName As String, _
'                     ByVal runSqlScriptDirective As CodeGenerationSupport.IRunSQLScriptDirective) _
'                     As String
'      Dim databaseName As String = runSqlScriptDirective.Database
'      Dim sqlText As String = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES " & _
'              " WHERE ROUTINE_CATALOG='" & databaseName & _
'              "' AND ROUTINE_TYPE='PROCEDURE'" & _
'              " AND ROUTINE_NAME='" & procName & "'"
'      Dim da As New Data.SqlClient.SqlDataAdapter(sqlText, mconnection)
'      Dim dt As New Data.DataTable
'      da.Fill(dt)
'      If dt.Rows.Count > 0 Then
'         Return dt.Rows(0)("ROUTINE_DEFINITION").ToString
'      End If
'      Return Nothing
'   End Function
'#End Region
'End Class
'End Namespace
